package com.cloud.portmana.entity.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.TimeZone;

import javax.management.Query;

import org.hibernate.Hibernate;
import org.hibernate.transform.Transformers;

import cn.jpush.api.report.UsersResult.User;

import com.cloud.portmana.dto.BgCycleThreeDS;
import com.cloud.portmana.entity.EntitySaleCityStatistics;
import com.cloud.portmana.entity.EntitySaleStatistics;
import com.cloud.portmana.entity.VehicleInfo;
import com.cloud.portmana.entity.dao.IBgStatisticsManaSaleDao;
import com.cloud.util.base.BaseHibernateDao;
import com.cloud.util.base.IUserInfo;
import com.cloud.util.date.DateUtil;
import com.cloud.util.other.StringUtil;
import com.cloud.util.page.PageInfo;

public class BgStatisticsManaSaleDao extends BaseHibernateDao<VehicleInfo, String> implements IBgStatisticsManaSaleDao {

	
	
	
	public List<List<EntitySaleStatistics>> getfourSalelist(IUserInfo userInfo){
		List<List<EntitySaleStatistics>>  fourlist = new ArrayList<List<EntitySaleStatistics>>();
	    Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT+08:00"));    //获取东八区时间

		  int year = c.get(Calendar.YEAR);    //获取年
		  int month = c.get(Calendar.MONTH) + 1;   //获取月份，0表示1月份
		  int day = c.get(Calendar.DAY_OF_MONTH);    //获取当前天数
		  List<EntitySaleStatistics> yearlist = new ArrayList<EntitySaleStatistics>();
		  List<EntitySaleStatistics> quarterlist = new ArrayList<EntitySaleStatistics>();
		  List<EntitySaleStatistics> monthlist = new ArrayList<EntitySaleStatistics>();
		  List<EntitySaleStatistics> daylist = new ArrayList<EntitySaleStatistics>();
		String yearsql = "SELECT count(*) 'value',province 'name' from `user_vehicle` where business_id ='"+userInfo.getUserId()+"' and create_time like '"+String.valueOf(year)+"%' group by province";
	    yearlist = this.getSessionFactory().getCurrentSession().createSQLQuery(yearsql).addEntity(EntitySaleStatistics.class).list();
		String quarterstrStar = "";
		String quarterstrend = "";
		if((month-1)/3==0){
			//第一季度
			quarterstrStar = year+"-01";
			quarterstrend = year+"-04";
		}else if((month-1)/3==1){
			quarterstrStar = year+"-04";
			quarterstrend = year+"-07";
		}else if((month-1)/3==2){
			quarterstrStar = year+"-07";
			quarterstrend = year+"-10";
		}else{
			quarterstrStar = year+"-10";
			quarterstrend = year+1+"";
		}
		
		String quartersql = "SELECT count(*) 'value',province 'name' from `user_vehicle` where business_id ='"+userInfo.getUserId()+"' and create_time > '"+quarterstrStar+"' and create_time < '"+quarterstrend+"'  group by province";
		quarterlist = this.getSessionFactory().getCurrentSession().createSQLQuery(quartersql).addEntity(EntitySaleStatistics.class).list();
		 
		String monthStr = ""+month;
		if(month<10){
			monthStr = "0"+month; 
		}
		
		String dayS = ""+day;
		if(day<10){
			dayS = "0"+day; 
		}
		
		String monthstr = year+"-"+monthStr;
		String monthsql = "SELECT count(*) 'value',province 'name' from `user_vehicle` where business_id ='"+userInfo.getUserId()+"' and create_time like '"+monthstr+"%' group by province";
	    monthlist = this.getSessionFactory().getCurrentSession().createSQLQuery(monthsql).addEntity(EntitySaleStatistics.class).list();
	    
		
		String daystr = year+"-"+monthStr+"-"+dayS;
		String daysql = "SELECT count(*) 'value',province 'name' from `user_vehicle` where business_id ='"+userInfo.getUserId()+"' and create_time like '"+daystr+"%' group by province";
		System.out.println(daysql+"====");
		daylist = this.getSessionFactory().getCurrentSession().createSQLQuery(daysql).addEntity(EntitySaleStatistics.class).list();
		
		fourlist.add(0, daylist);
		fourlist.add(1, monthlist);
		fourlist.add(2, quarterlist);
		fourlist.add(3, yearlist);
		
		return fourlist;
	}
	
	@Override
	public PageInfo getPageInfoCity(PageInfo pageInfo,IUserInfo userInfo,String province,String type, BgCycleThreeDS cycleThreeDS){
		
		
		String hsql = "select vi.vehicle_brand vbrand,vi.vehicle_type vtype,city,count(*) sum from vehicle_info vi,user_vehicle uv where uv.business_id='"+userInfo.getUserId()+"' and uv.vehicle_info_id = vi.ID ";
		String countSql = "select count(*) from (select city from vehicle_info vi,user_vehicle uv where uv.business_id='"+userInfo.getUserId()+"' and uv.vehicle_info_id = vi.ID ";
		String whereSql = " ";
		String orderSql = " group BY city ";
		String ss = ") as tmp";
		Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT+08:00"));    //获取东八区时间

		  int year = c.get(Calendar.YEAR);    //获取年
		  int month = c.get(Calendar.MONTH) + 1;   //获取月份，0表示1月份
		  int day = c.get(Calendar.DAY_OF_MONTH);    //获取当前天数
		  String monthStr = ""+month;
			if(month<10){
				monthStr = "0"+month; 
			}
			
			String dayS = ""+day;
			if(day<10){
				dayS = "0"+day; 
			}
			String monthstr = year+"-"+monthStr;
			
			String quarterstrStar = "";
			String quarterstrend = "";
			if((month-1)/3==0){
				//第一季度
				quarterstrStar = year+"-01";
				quarterstrend = year+"-04";
			}else if((month-1)/3==1){
				quarterstrStar = year+"-04";
				quarterstrend = year+"-07";
			}else if((month-1)/3==2){
				quarterstrStar = year+"-07";
				quarterstrend = year+"-10";
			}else{
				quarterstrStar = year+"-10";
				quarterstrend = year+1+"";
			}
			
			String daystr = year+"-"+monthStr+"-"+dayS;
		if(type !=null){
			if("year".equals(type)){
				whereSql+=" and create_time like '"+String.valueOf(year)+"%'";
			}else if("month".equals(type)){			
				whereSql+=" and create_time like '"+monthstr+"%'";				    
			}else if("quarter".equals(type)){				
				whereSql+=" and create_time > '"+quarterstrStar +"' and create_time < '"+quarterstrend+"'";				 
			}else if("day".equals(type)){				
				whereSql+=" and create_time like '"+daystr+"%'";				
			} 
		}
		if(!StringUtil.isEmpty(province)){
			whereSql+=" and uv.province='"+province+"'";
		}
		
		if(cycleThreeDS!=null){
			if(!StringUtil.isEmpty(cycleThreeDS.getOneField())){
				whereSql+=" and uv.province='"+cycleThreeDS.getOneField()+"'";
			}
			if(!StringUtil.isEmpty(cycleThreeDS.getTwoField())){
				whereSql+=" and uv.city='"+cycleThreeDS.getTwoField()+"'";
			}
			//时间
			if(!StringUtil.isEmpty(cycleThreeDS.getUpdateTimestart()) && !StringUtil.isEmpty(cycleThreeDS.getUpdateTimeend())){//有开始时间和结束时间
				if(DateUtil.compareDateYYYYMMDD(cycleThreeDS.getUpdateTimestart(), cycleThreeDS.getUpdateTimeend())){//开始时间大于结束时间
					whereSql += " and create_time >= '" + cycleThreeDS.getUpdateTimeend() + " 00:00:00'"
							 + " and create_time <= '" + cycleThreeDS.getUpdateTimestart() + " 23:59:59'";
				}else{
					whereSql += " and create_time <= '" + cycleThreeDS.getUpdateTimeend() + " 23:59:59'"
							 + " and create_time >= '" + cycleThreeDS.getUpdateTimestart() + " 00:00:00'";
				}
			}else if(!StringUtil.isEmpty(cycleThreeDS.getUpdateTimestart())){//只有开始时间
				whereSql += " and create_time >= '" + cycleThreeDS.getUpdateTimestart() + " 00:00:00'";
			}else if(!StringUtil.isEmpty(cycleThreeDS.getUpdateTimeend())){//只有结束时间
				whereSql += " and create_time <= '" + cycleThreeDS.getUpdateTimeend() + " 23:59:59'";
			}
		}
		
		
		
		pageInfo.setEntityClass(EntitySaleCityStatistics.class);
		pageInfo.setListHbSql(hsql+whereSql+orderSql);
		pageInfo.setTotalCountHbSQL(countSql+whereSql+orderSql+ss);
		return pageInfo;
		
		 
		
		
	}
	
	
	
	
	
//	@Override
//	public PageInfo getPageInfo(PageInfo pageInfo, BgCycleThreeDS cycleThreeDS, IUserInfo userInfo) {
//		String hsql = "select * from vehicle_info where business_id = '"+userInfo.getUserId() +"' ";
//		String countSql = "select COUNT(*) from vehicle_info where business_id = '"+userInfo.getUserId() +"' ";
//		String whereSql = "  ";
//		String orderSql = " ORDER BY vehicle_info_time desc";
//		
//		if(cycleThreeDS !=null){		
//			
////			//获取车型的品牌
////			if(!StringUtil.isEmpty(cycleThreeDS.getOneField())){
////				whereSql += " and thief_name like '%" + StringUtil.addQuote(cycleThreeDS.getOneField()) + "%'";
////			}
////			//获取车型的型号
////			if(!StringUtil.isEmpty(cycleThreeDS.getTwoField())){
////				whereSql += " and imie like '%" + StringUtil.addQuote(cycleThreeDS.getTwoField()) + "%'";
////			}
//			//时间
//			if(!StringUtil.isEmpty(cycleThreeDS.getUpdateTimestart()) && !StringUtil.isEmpty(cycleThreeDS.getUpdateTimeend())){//有开始时间和结束时间
//				if(DateUtil.compareDateYYYYMMDD(cycleThreeDS.getUpdateTimestart(), cycleThreeDS.getUpdateTimeend())){//开始时间大于结束时间
//					whereSql += " and create_time >= '" + cycleThreeDS.getUpdateTimeend() + " 00:00:00'"
//							 + " and create_time <= '" + cycleThreeDS.getUpdateTimestart() + " 23:59:59'";
//				}else{
//					whereSql += " and create_time <= '" + cycleThreeDS.getUpdateTimeend() + " 23:59:59'"
//							 + " and create_time >= '" + cycleThreeDS.getUpdateTimestart() + " 00:00:00'";
//				}
//			}else if(!StringUtil.isEmpty(cycleThreeDS.getUpdateTimestart())){//只有开始时间
//				whereSql += " and create_time >= '" + cycleThreeDS.getUpdateTimestart() + " 00:00:00'";
//			}else if(!StringUtil.isEmpty(cycleThreeDS.getUpdateTimeend())){//只有结束时间
//				whereSql += " and create_time <= '" + cycleThreeDS.getUpdateTimeend() + " 23:59:59'";
//			}
//		}
//		pageInfo.setEntityClass(VehicleInfo.class);
//		pageInfo.setListHbSql(hsql+whereSql+orderSql);
//		pageInfo.setTotalCountHbSQL(countSql+whereSql);
//		return pageInfo;
//	}
//
//	@Override
//	public String getAllsumOfflineByChejiaMark(String chejiaMark) {
//		// TODO Auto-generated method stub
//		
//		String Allsumsql="select count(*) from imei_info_new where imei in (select imie from user_vehicle where chejia_num like '"+chejiaMark+"%') ";
//		
//		String Offlinesql = Allsumsql+ " and statistic_behavior = 2";
//		System.out.println(Offlinesql);
//		System.out.println(Allsumsql);
//		
//		String Allsum =this.getSessionFactory().getCurrentSession().createSQLQuery(Allsumsql).list().get(0).toString();
//		String Offline = this.getSessionFactory().getCurrentSession().createSQLQuery(Offlinesql).list().get(0).toString();
//		
//		
//		return Allsum+","+Offline;
//	}

}
